package yc.tool.dbmigrate.dao.impl;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;

import cn.hutool.core.convert.Convert;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import yc.tool.dbmigrate.common.Constant;
import yc.tool.dbmigrate.dao.AbstractCommonDao;
import yc.tool.dbmigrate.entity.FieldInfo;
import yc.tool.dbmigrate.entity.TableInfo;

@Repository("sqlserverDao")
public class SqlserverDaoImpl extends AbstractCommonDao {
	
	private final Logger log = LoggerFactory.getLogger(this.getClass());
	
	@Resource(name="jdbcTemplate_sqlserver_source")
    private JdbcTemplate jdbc_source;
	@Resource(name="jdbcTemplate_sqlserver_tag")
    private JdbcTemplate jdbc_tag;
	
	@Override
	public void init() {
		jdbcTemplate_source = jdbc_source;
		jdbcTemplate_tag = jdbc_tag;
	}

	@Override
	public List<Map<String, Object>> selectOnePageData(TableInfo tableInfo, Integer pageNumber, Integer pageSize)
			throws Exception {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT TOP ");
		sb.append(pageSize);
		sb.append(" ");
		
		StringBuffer whereSb = new StringBuffer();
		List<FieldInfo> fields = tableInfo.getFieldInfos();
		FieldInfo itemField = null;
		for (int i = 0; i < fields.size(); i++) {
			itemField = fields.get(i);
			sb.append(handleKeyWordInSql(itemField.getSourceFieldName()));
			if (i < (fields.size() - 1)) {
				sb.append(", ");
			}
			//查询条件
			if (!StringUtils.isEmpty(itemField.getSourceWhere())) {
				whereSb.append(" ");
				whereSb.append(itemField.getSourceFieldName());
				whereSb.append(itemField.getSourceWhere().getShortSql());
			}
		}
		sb.append(" from ");
		sb.append(tableInfo.getSourceTableName());
		sb.append(" where ");
		sb.append(tableInfo.getSourceTableIdName());
		sb.append(" NOT IN (SELECT TOP ");
		sb.append((pageNumber-1)*pageSize);
		sb.append(" ");
		sb.append(tableInfo.getSourceTableIdName());
		sb.append(" FROM ");
		sb.append(tableInfo.getSourceTableName());
		//--WHERE
		if (whereSb.length() > 0) {
			sb.append(" where ");
			sb.append(whereSb);
		}
		//--
		sb.append(" ORDER BY ");
		sb.append(tableInfo.getSourceTableIdName());
		sb.append(")");
		//--WHERE
		if (whereSb.length() > 0) {
			sb.append(" and ");
			sb.append(whereSb);
		}
		//--
		sb.append(" ORDER BY ");
		sb.append(tableInfo.getSourceTableIdName());
		
		
		
		String selectSql = sb.toString();
		log.info("sqlserver select sql: {}", selectSql);
		
		return jdbc_source.queryForList(selectSql);
	}


	@Override
	public String handleKeyWordInSql(String fieldName) throws Exception {
		for (String item : Constant.KEYWORD_SQLSERVER) {
			if (item.equals(fieldName.toLowerCase())) {
				return "\""+fieldName.toUpperCase()+"\"";
			}
		}
		return fieldName;
	}


	@Override
	public void handleParamsForInsertFromSqlserver(PreparedStatement ps, Map<String, Object> itemData,
			TableInfo tableInfo) throws Exception {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void handleParamsForInsertFromOracle(PreparedStatement ps, Map<String, Object> itemData, TableInfo tableInfo)
			throws Exception {
		List<FieldInfo> fieldInfo = tableInfo.getFieldInfos();
		
		FieldInfo itemFieldInfo = null;
		String itemTagFieldType = null;
		Object itemTagFieldValue = null;
		for (int i = 0; i < fieldInfo.size(); i++) {
			itemFieldInfo = fieldInfo.get(i);
			itemTagFieldValue = itemData.get(itemFieldInfo.getSourceFieldName());
			itemTagFieldType = itemFieldInfo.getTagFieldType().toLowerCase();
			
			if (itemTagFieldType.indexOf("int") != -1) {
				if (null != itemTagFieldValue) {
					ps.setInt(i+1, Integer.parseInt(itemTagFieldValue.toString()));
				} else {
					ps.setString(i+1, null);
				}
			} else if (itemTagFieldType.indexOf("char") != -1 || itemTagFieldType.indexOf("text") != -1 || itemTagFieldType.indexOf("clob") != -1) {
				if (itemFieldInfo.getTagFieldNullable()) {
					ps.setString(i+1,(itemTagFieldValue==null)?null:itemTagFieldValue.toString());
				} else {
					ps.setString(i+1,(null == itemTagFieldValue || "".equals(itemTagFieldValue.toString()))?" ":itemTagFieldValue.toString());
				}
			} else if (itemTagFieldType.indexOf("date") != -1 || itemTagFieldType.indexOf("timestamp") != -1) {//可能出错
				
//				ps.setDate(i+1, (itemTagFieldValue==null)?null:(new Date(((Timestamp)itemTagFieldValue).getTime())));
				ps.setTimestamp(i+1, (itemTagFieldValue==null)?null:((Timestamp)itemTagFieldValue));
			} else if (itemTagFieldType.indexOf("number") != -1 || itemTagFieldType.indexOf("float") != -1 || itemTagFieldType.indexOf("real") != -1 || itemTagFieldType.indexOf("decimal") != -1) {
				if (null != itemTagFieldValue) {
					ps.setDouble(i+1, ((BigDecimal)itemTagFieldValue).doubleValue());//可能出错
				} else {
					ps.setString(i+1, null);
				}
			} else if (itemTagFieldType.indexOf("bit") != -1) {
				if (null != itemTagFieldValue) {
					ps.setBoolean(i+1, (((BigDecimal)itemTagFieldValue).intValue() == 0)?false:true);
				} else {
					ps.setString(i+1, null);
				}
				
			}
		}
		
	}

	@Override
	public void handleParamsForInsertFromMysql(PreparedStatement ps, Map<String, Object> itemData, TableInfo tableInfo)
			throws Exception {
        List<FieldInfo> fieldInfo = tableInfo.getFieldInfos();
        int size = fieldInfo.size();
        for (int i = 1; i <= size; i++) {
            FieldInfo itemFieldInfo = fieldInfo.get(i - 1);
            // sqlServer字段值
            Object itemTagFieldValue = itemData.get(itemFieldInfo.getSourceFieldName());
            // sqlServer字段类型
            String itemTagFieldType = itemFieldInfo.getTagFieldType().toLowerCase();
            ps.setObject(i, null);
            if (itemTagFieldValue != null) {
                // 根据字段类型插入数据
                if (itemTagFieldType.contains("int")) {
                    ps.setInt(i, Integer.parseInt(itemTagFieldValue.toString()));
                } else if (itemTagFieldType.contains("char") || itemTagFieldType.contains("text")) {
                    ps.setString(i, Convert.toStr(itemTagFieldValue, itemFieldInfo.getTagFieldNullable() ? null : " "));
                } else if (itemTagFieldType.contains("date") || itemTagFieldType.contains("time")) {
                    Date date = Convert.toDate(itemTagFieldValue);
                    ps.setTimestamp(i, date == null ? null : new Timestamp(date.getTime()));
                } else if (itemTagFieldType.contains("number") || itemTagFieldType.contains("float")
                        || itemTagFieldType.contains("real") || itemTagFieldType.contains("decimal")
                        || itemTagFieldType.contains("numeric")) {
                    ps.setBigDecimal(i, Convert.toBigDecimal(itemTagFieldValue));
                } else if (itemTagFieldType.contains("bit")) {
                    ps.setBoolean(i, new BigDecimal(itemTagFieldValue.toString()).intValue() != 0);
                }
            }
        }
	}

}
